﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class Lop
    {
        string _MaLop;

        public string MaLop
        {
            get { return _MaLop; }
            set { _MaLop = value; }
        }
        string _TenLop;
        public string TenLop
        {
            get { return _TenLop; }
            set { _TenLop = value; }
        }
        private int _MaHe;
        public int MaHe
        {
            get { return _MaHe; }
            set { _MaHe = value; }
        }

        public Lop()
        { }
        public static List<Lop> list;
        public Lop(string ms, string sten, int mahe)
        {
            MaLop = ms;
            TenLop = sten;
            MaHe = mahe;
        }
        public static Lop GetData(SqlDataReader read)
        {
            Lop l = new Lop();
            l.MaLop = CType.Tostring(read["MaLop"]);
            l.TenLop = CType.Tostring(read["TenLop"]);
            l.MaHe = CType.ToInt32(read["MaHe"]);
            return l;
        }
        public static List<Lop> All()
        {
            list = new List<Lop>();
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from Lop";
                conn.Open();
                SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (read.Read())
                {
                    Lop l = GetData(read);
                    list.Add(l);
                }
                return list;
            }
        }
        public static List<Lop> LayDSTheoHeDaoTao(int nhom )
        {
            list = new List<Lop>();
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Lop_LayDSTheoHeDaoTao";
                cmd.Parameters.Add("@Nhom", SqlDbType.Int).Value = nhom;
                conn.Open();
                SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (read.Read())
                {
                    Lop l = GetData(read);
                    list.Add(l);
                }
                return list;
            }
        }

        public static int ThemLop(Lop l)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "Lop_Them";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MaLop", SqlDbType.VarChar, 10).Value = l.MaLop;
                cmd.Parameters.Add("@TenLop", SqlDbType.NVarChar, 50).Value = l.TenLop;
                cmd.Parameters.Add("@MaHe", SqlDbType.Int).Value = l.MaHe;

                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        public static int XoaLop(string ms)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "Lop_Xoa";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MaLop", SqlDbType.VarChar, 10).Value = ms;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        public static int CapNhatLop(Lop l)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "Lop_CapNhat";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MaLop", SqlDbType.VarChar, 10).Value = l.MaLop;
                cmd.Parameters.Add("@TenLop", SqlDbType.NVarChar, 50).Value = l.TenLop;
                cmd.Parameters.Add("@MaHe", SqlDbType.Int).Value = l.MaHe;

                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }

        public static List<Lop> TimLopTheoHe(int mahe)
        {
            List<Lop> list = new List<Lop>();
            using (SqlConnection cnn = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("Lop_TimTheoHe", cnn);
                cmd.Parameters.Add("@MaHe", SqlDbType.Int).Value = mahe;
                cnn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        Lop data = GetData(reader);
                        list.Add(data);
                    }
                }
                return list;
            }
        }
        public static Lop TimLopTheoTen(string ten)
        {
            Lop lop = new Lop();
            using (SqlConnection cnn = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("Lop_TimTheoTen", cnn);
                cmd.Parameters.Add("@TenLop", SqlDbType.NVarChar, 50).Value = ten;
                cnn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                        lop = GetData(reader);
                }
                return lop;
            }
        }

    }
}
